// Project: Salient
// http://salient.codeplex.com
// 
// Copyright 2010, Sky Sanders <sky at skysanders.net>
// Dual licensed under the MIT or GPL Version 2 licenses.
// http://salient.codeplex.com/license
// 
// Date: April 25 2010 

#region

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;

#endregion

namespace Salient.Web.UserFiles
{
    ///<summary>
    ///</summary>
    public class SQLiteLoader
    {
        private string _connectionString, _path;

        ///<summary>
        ///</summary>
        ///<param name="path"></param>
        public SQLiteLoader(string path)
        {
            EnsureConnection(path);
        }

        /// <summary>
        /// Deletes and recreates db file
        /// </summary>
        public void Clear()
        {
            File.Delete(_path);
            EnsureConnection(_path);
        }

        /// <summary>
        /// Creates and initializes db file if it does not exist and returns connection string.
        /// </summary>
        /// <param name="path"></param>
        /// TODO: include root dir creation in DDL, remove call to save and make this static
        private void EnsureConnection(string path)
        {
            _path = path;

            SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder
                {DataSource = path, Version = 3, FailIfMissing = false};
            _connectionString = csb.ConnectionString;
            if (!File.Exists(path))
            {
                using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
                {
                    using (SQLiteCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandText = Ddl;
                        cmd.ExecuteNonQuery();
                    }
                }
                EntryInfo entry = new EntryInfo {Path = "/", IsDirectory = true, EntryId = Guid.NewGuid()};
                SaveEntry(entry, false);
            }
        }

        /// <summary>
        /// Returns an entry for specified directory, creating and nesting segments as needed.
        /// </summary>
        /// <param name="path"></param>
        /// <returns>Directory entry</returns>
        public EntryInfo EnsureDirectory(string path)
        {
            path = Helpers.FixPath(path);
            EntryInfo parentEntry = GetEntry(path, false);
            if (parentEntry == null)
            {
                string parentPath = Helpers.FixPath(Path.GetDirectoryName(path));
                EntryInfo entryGrandParent = EnsureDirectory(parentPath);
                parentEntry = new EntryInfo {Path = path, ParentEntryId = entryGrandParent.EntryId, IsDirectory = true};
                SaveEntry(parentEntry, false);
            }
            return parentEntry;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        /// <param name="data"></param>
        /// <returns>Inserted or updated entry</returns>
        public EntryInfo SaveEntry(string path, byte[] data)
        {
            path = Helpers.FixPath(path);
            EntryInfo entry = GetEntry(path, false);

            if (entry != null)
            {
                entry.Data = data;
                entry.LastWriteTimeUtc = DateTime.Now.ToUniversalTime();
                SaveEntry(entry, true);
            }
            else
            {
                string entryParentPath = Helpers.FixPath(Path.GetDirectoryName(path));
                EntryInfo parentEntry = EnsureDirectory(entryParentPath);

                parentEntry.LastWriteTimeUtc = DateTime.Now.ToUniversalTime();
                SaveEntry(parentEntry, true);

                entry = new EntryInfo
                    {
                        Path = path,
                        ParentEntryId = parentEntry.EntryId,
                        Data = data
                    };
                SaveEntry(entry, false);
            }
            return entry;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        /// <param name="includeData"></param>
        /// <returns>Null if not found</returns>
        public EntryInfo GetEntry(string path, bool includeData)
        {
            path = Helpers.FixPath(path);
            EntryInfo entry = null;
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = (includeData ? SelectSql : SelectSqlNoData) + " WHERE (Path = @Path)";
                    cmd.Parameters.AddWithValue("@Path", path);
                    conn.Open();
                    SQLiteDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        entry = new EntryInfo(reader);
                    }
                }
            }
            return entry;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id">If Guid.Empty root, '/', is returned.</param>
        /// <param name="includeData"></param>
        /// <returns>Null if not found</returns>
        public EntryInfo GetEntry(Guid id, bool includeData)
        {
            if (id.Equals(Guid.Empty))
            {
                return GetEntry("/", includeData);
            }

            EntryInfo entry = null;
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = (includeData ? SelectSql : SelectSqlNoData) + " WHERE (EntryId = @EntryId)";
                    cmd.Parameters.AddWithValue("@EntryId", id);
                    conn.Open();
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            entry = new EntryInfo(reader);
                        }
                    }
                }
            }
            return entry;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="id">If Guid.Empty root, '/', is returned.</param>
        /// <param name="includeData"></param>
        /// <param name="includeFiles"></param>
        /// <param name="includeDirectories"></param>
        /// <param name="recurse"></param>
        /// <returns></returns>
        public EntryInfo GetEntry(Guid id, bool includeData, bool includeFiles, bool includeDirectories, bool recurse)
        {
            EntryInfo entry = GetEntry(id, false);
            if (entry == null)
            {
                throw new FileNotFoundException();
            }

            List<EntryInfo> children = new List<EntryInfo>();

            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    //TODO: use subquery to get children
                    // eg SELECT (SELECT name FROM food_types WHERE id=f.type_id) Type,
                    cmd.CommandText = (includeData ? SelectSql : SelectSqlNoData) +
                                      " WHERE (ParentEntryId = @ParentEntryId) AND (" +
                                      (includeFiles ? " IsDirectory=0" : "1=0") + " OR " +
                                      (includeDirectories ? " IsDirectory=1" : "1=0") +
                                      ") ORDER BY IsDirectory DESC, Path ASC;";

                    cmd.Parameters.AddWithValue("@ParentEntryId", entry.EntryId);
                    conn.Open();
                    SQLiteDataReader rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        EntryInfo child = new EntryInfo(rdr);
                        child.HasChildren = EntryHasChildren(child.EntryId, includeFiles, includeDirectories);
                        children.Add(child);
                    }
                    rdr.Close();
                    entry.Children = children.ToArray();
                    entry.HasChildren = children.Count > 0;
                }
                conn.Close();
            }

            if (recurse)
            {
                for (int i = 0; i < entry.Children.Length; i++)
                {
                    if (entry.Children[i].HasChildren)
                    {
                        entry.Children[i] = GetEntry(entry.Children[i].EntryId, includeData, includeFiles,
                                                     includeDirectories,
                                                     true);
                    }
                }
            }
            return entry;
        }

        ///<summary>
        ///</summary>
        ///<param name="entry"></param>
        ///<param name="list"></param>
        public static void FlattenEntry(EntryInfo entry, ICollection<EntryInfo> list)
        {
            list.Add(entry);

            if (entry.Children != null && entry.Children.Length > 0)
            {
                for (int i = 0; i < entry.Children.Length; i++)
                {
                    FlattenEntry(entry.Children[i], list);
                }
            }
            entry.Children = null;
        }


        ///<summary>
        ///</summary>
        ///<param name="id"></param>
        public void DeleteEntry(Guid id)
        {
            EntryInfo entry = GetEntry(id, false, true, true, true);
            if (entry.Path == "/")
            {
                Clear();
                return;
            }

            DeleteEntry(entry);
        }


        private void DeleteEntry(EntryInfo entry)
        {
            if (entry.Children != null && entry.Children.Length > 0)
            {
                foreach (EntryInfo child in entry.Children)
                {
                    DeleteEntry(child);
                }
            }

            DeleteEntryChildrenFirst(entry.EntryId);
        }


        private void DeleteEntryChildrenFirst(Guid id)
        {
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText =
                        "DELETE FROM Entries WHERE ParentEntryId=@EntryId;DELETE FROM Entries WHERE EntryId=@EntryId;";

                    cmd.Parameters.AddWithValue("@EntryId", id);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private bool EntryHasChildren(Guid id, bool includeFiles, bool includeDirs)
        {
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText =
                        "SELECT COUNT(*) AS count FROM Entries WHERE (ParentEntryId = @ParentEntryId) AND (" +
                        (includeFiles ? " IsDirectory=0" : "1=0") + " OR " + (includeDirs ? " IsDirectory=1" : "1=0") +
                        ");";
                    cmd.Parameters.AddWithValue("@ParentEntryId", id);
                    conn.Open();
                    return ((long) cmd.ExecuteScalar()) > 0;
                }
            }
        }

        private void SaveEntry(EntryInfo entry, bool update)
        {
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = update ? UpdateSql : InsertSql;
                    cmd.Parameters.AddWithValue("@IsDirectory", entry.IsDirectory);
                    cmd.Parameters.AddWithValue("@Path", entry.Path);
                    cmd.Parameters.AddWithValue("@CreationTimeUtc", entry.CreationTimeUtc);
                    cmd.Parameters.AddWithValue("@LastWriteTimeUtc", entry.LastWriteTimeUtc);
                    cmd.Parameters.AddWithValue("@Data", entry.IsDirectory ? null : entry.Data);
                    cmd.Parameters.AddWithValue("@ParentEntryId", entry.ParentEntryId);
                    cmd.Parameters.AddWithValue("@EntryId", entry.EntryId);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        #region Constants

        private const string Ddl =
            @"
        PRAGMA foreign_keys=OFF;
        BEGIN TRANSACTION;
        CREATE TABLE [Entries] (
            [EntryId] guid PRIMARY KEY NOT NULL,
            [IsDirectory] bit NOT NULL DEFAULT 0,
            [Path] nvarchar NOT NULL,
            [CreationTimeUtc] datetime NOT NULL,
            [LastWriteTimeUtc] datetime NOT NULL,
            [Data] image,
            [ParentEntryId] guid
        );
        CREATE UNIQUE INDEX [IX_Path] ON [Entries] ([Path]);
        COMMIT;";

        private const string InsertSql =
            "INSERT INTO Entries (IsDirectory, Path, CreationTimeUtc, LastWriteTimeUtc, Data, ParentEntryId ,EntryId) values (@IsDirectory, @Path, @CreationTimeUtc, @LastWriteTimeUtc, @Data, @ParentEntryId, @EntryId)";

        private const string SelectSql =
            "SELECT EntryId, IsDirectory, Path, CreationTimeUtc, LastWriteTimeUtc, Data, ParentEntryId FROM Entries";

        private const string SelectSqlNoData =
            "SELECT EntryId, IsDirectory, Path, CreationTimeUtc, LastWriteTimeUtc, null as Data,ParentEntryId FROM Entries";

        private const string UpdateSql =
            "UPDATE Entries SET IsDirectory = @IsDirectory, Path = @Path, CreationTimeUtc = @CreationTimeUtc, LastWriteTimeUtc = @LastWriteTimeUtc, Data = @Data, ParentEntryId = @ParentEntryId WHERE     (Entries.EntryId = @EntryId)";

        #endregion
    }
}